
package cn.hpclub.server.service;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.util.InterfaceUtil;
import cn.hpclub.server.util.excel.ExportUtils;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @FileName: BankCardBindService.java
 * @Author zhl
 * @Description:
 * @Date 2016年12月14日 下午2:38:57
 * @CopyRight ZTE Corporation
 */
public class BankCardBindService {

    private static final Logger    logger  = LoggerFactory.getLogger(BankCardBindService.class);
    public static BankCardBindService service = new BankCardBindService();

    public String  getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        String sqlExceptSelect = " from bank_card_bind b";
        sqlExceptSelect += " left join member_org m on b.login_no=m.emp_code left join organization o on m.org_id=o.id  left join organization oc on o.CityCode=oc.id left join organization oa ON o.AreaCode = oa.id ";
        sqlExceptSelect += " where 1=1 ";

        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQuery(item);
        }
        sqlExceptSelect += " group by b.id order by b.last_modify_time desc ";

        return sqlExceptSelect;
    }

    private String buildQuery(Entry<String, String> item){
        if("start_time".equalsIgnoreCase(item.getKey())){
            String start_time = "'" + item.getValue().replaceAll(" ", "") + " 00:00:00'";
            return " and b.create_time >= " + start_time + " ";
        }
        if("end_time".equalsIgnoreCase(item.getKey())){
            String end_time = "'" + item.getValue().replaceAll(" ", "") + " 23:59:59'";
            return " and b.create_time <= " + end_time + " ";
        }
        if("login_no".equalsIgnoreCase(item.getKey())){
            return " and binary b." + item.getKey() + " like '%" + item.getValue() + "%'";
        }
        if("city_id".equalsIgnoreCase(item.getKey())){
            //String city_id = "" + item.getValue().replaceAll(" ", "") + "";
            return "";
        }
        if("group_id".equalsIgnoreCase(item.getKey())){
            String group_id = item.getValue();
            return " and oc.NumCode =" + group_id + " ";
        }
        return " and b." + item.getKey() + " like '%" + item.getValue() + "%'";
    }



    /**
     * @describe 获取基础拜访列表的excel文件
     * @param searchInfo 查询条件参数列表
     * @return SXSSFWorkbook
     */
    public SXSSFWorkbook getExcel(ConcurrentHashMap<String, String> searchInfo){
        SXSSFWorkbook workbook = null;
        String sql = " SELECT b.*,oc.DisplayName as cmcc_name,oa.DisplayName as displayName FROM bank_card_bind b ";
        sql += " left join member_org m on b.login_no=m.emp_code left join organization o on m.org_id=o.id  left join organization oc on o.CityCode=oc.id left join organization oa ON o.AreaCode = oa.id ";
        sql += "  WHERE 1=1 ";
        for(Entry<String, String> item : searchInfo.entrySet()){
            sql += buildQuery(item);
        }
        sql += " group by b.id order by b.last_modify_time desc,b.create_time desc ";
        List<Record> seq = Db.find(sql);
        String[] excelHeaders = new String[]{"开户名","银行卡号","开户行","开户地","支行信息","工号","地市","区县","银行代码",
                "手机号码","数据保存类型","创建时间","最后修改时间"};
        ExportUtils exportUtils = new ExportUtils(excelHeaders);
        String[] keys = new String[]{"account_name","card_number","regist_bank","regist_area","subbranch","login_no","cmcc_name","displayName",
                "bank_type",
                "phone_no","busi_flag","create_time","last_modify_time"};


        try {
            exportUtils.exportExcel(keys,seq,"yyyy-MM-dd HH:ss:mm ",1);//此处最后一个参数表示从第一行开始填入数据（表头为第0行）
            workbook = exportUtils.getSXSSFWorkbook();
        } catch (FileNotFoundException e) {
            logger.error("BankCardBindService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an FileNotFoundException,error message:"+e.getMessage());
        } catch (IOException ex) {
            logger.error("BankCardBindService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an IOException,error message:"+ex.getMessage());
        }
        return workbook;
    }










}
